package com.coolgor.airvow.SQL;

import java.sql.ResultSet;
import java.sql.SQLException;

/**暂时注释掉这个类
import com.DrKoo.File.Office.ExcelOperation;
import com.DrKoo.File.Office.OfficeOperationFactory;
import com.DrKoo.SQL.DataOperation;
import com.DrKoo.SQL.DataTable;
*/

public class ExportData {
	/**
	public boolean exportExcel(DataOperation db, String tableName, String whereStr, String orderStr, String xlsFileName, String sheetName, int buffer) 
			throws SQLException, InterruptedException
	{
		boolean res = false;
		//int buff = buffer;
		ExcelOperation xls = OfficeOperationFactory.createExcelOperation();
		DataTable dt = new DataTable();
		String sql = "";
		
		String whereString ;
		if (null==whereStr || "".equals(whereStr)) whereString = "1=1";
		else whereString = whereStr;
		String orderString = null ;
		if (null==orderStr || "".equals(orderStr)) {
			sql = "select column_name from all_tab_columns where table_name='"+tableName.toUpperCase()+"'";
			ResultSet rs;
			try {
				rs = db.executeRS(sql);
				rs.next();
				orderString = rs.getString(1);
				rs.close();
			} catch (InterruptedException e1) {
				// TODO Auto-generated catch block
				e1.printStackTrace();
			}  catch (SQLException e) {
				orderString = "";
				e.printStackTrace();
			}
		}else orderString = orderStr;
		
		int dataRow = 0;
		try {
			sql = "select count(1) from "+tableName+" where "+whereString;
			ResultSet rs = db.executeRS(sql);
			rs.next();
			dataRow = Integer.parseInt(rs.getString(1));
			rs.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
//		sql = "select * from "+tableName+" where ROWNUM<="+(buff-1)+" and "+whereString ;
//		if (!"".equals(orderString)) sql = sql + " order by "+orderString;

		sql="SELECT t.* FROM " +
				"(SELECT t1.*,row_number() over(ORDER BY "+orderString+") AS num FROM "+tableName+" t1 WHERE "+whereString+") t " +
						"WHERE num BETWEEN 1 AND "+(buffer-1);
		dt.Create(db.executeRS(sql));
		xls.DataTableToExcel(dt, xlsFileName, sheetName, true, 1, 1);
		dataRow = dataRow-buffer+1;
		int dataRowPoint = buffer+1;
		while(dataRow>0){
//			System.out.println("----->"+dataRow+"-->"+dataRowPoint);
			dt.clear();
			sql="SELECT t.* FROM " +
					"(SELECT t1.*,row_number() over(ORDER BY "+orderString+") AS num FROM "+tableName+" t1 WHERE "+whereString+") t " +
							"WHERE num BETWEEN "+(dataRowPoint-1)+" AND "+(dataRowPoint+buffer);
			dt.Create(db.executeRS(sql));
			xls.DataTableToExcel(dt, xlsFileName, sheetName, false, dataRowPoint, 1);
			dataRow = dataRow - buffer;
			dataRowPoint = dataRowPoint+buffer;
		}
		
		return res;
	}	
	
	
	public boolean exportExcel(DataOperation db, String sql, String xlsFileName, String sheetName, int buffer) 
			throws SQLException, InterruptedException
	{
		boolean res = false;
		//int buff = buffer;
		ExcelOperation xls = OfficeOperationFactory.createExcelOperation();
		DataTable dt = new DataTable();
		String sqlString = "";
		
		
		int dataRow = 0;
		try {
			sqlString = "select count(1) from ("+sql+") ";
			ResultSet rs = db.executeRS(sqlString);
			rs.next();
			dataRow = Integer.parseInt(rs.getString(1));
			rs.close();
		} catch (Exception e) {
			e.printStackTrace();
		}

		sqlString = "SELECT t.* FROM " +
				"(SELECT t1.*,row_number() over(ORDER BY null) AS num FROM ("+sql+") t1 ) t " +
						"WHERE num BETWEEN 1 AND "+(buffer-1);
		dt.Create(db.executeRS(sqlString));
		xls.DataTableToExcel(dt, xlsFileName, sheetName, true, 1, 1);
		dataRow = dataRow-buffer+1;
		int dataRowPoint = buffer+1;
		while(dataRow>0){
//			System.out.println("----->"+dataRow+"-->"+dataRowPoint);
			dt.clear();
			sqlString = "SELECT t.* FROM " +
					"(SELECT t1.*,row_number() over(ORDER BY null) AS num FROM ("+sql+") t1 ) t " +
							"WHERE num BETWEEN "+(dataRowPoint-1)+" AND "+(dataRowPoint+buffer);
			dt.Create(db.executeRS(sqlString));
			xls.DataTableToExcel(dt, xlsFileName, sheetName, false, dataRowPoint, 1);
			dataRow = dataRow - buffer;
			dataRowPoint = dataRowPoint+buffer;
		}
		
		return res;
	}
	
*/

}
